set more off
clear all



use cex_for_processing, clear


replace fincatax=0 if fincatax==.

replace inc_hrs1=0 if inc_hrs1==.
replace inc_hrs2=0 if inc_hrs2==.
replace incweek1=0 if incweek1==.
replace incweek2=0 if incweek2==.

replace dmsxcc=dmsxcc+dmsxc2
replace furnsh=furnsh+furns2
replace equpmt=equpmt+equpm2
replace vehpch=vehpch+vehpc2
replace cashco=cashco-alchcl
gen housop=dmsxcc+bbyday
gen adultcl=mensix+womsix
gen shoesoth=footwr+othapl
gen oppub=vehopr+pubtra


/*reqown adjustment as in Aguir-Bils: START*/
gen totexp_fi1=0
gen reqown_fi=0
gen m2=0
gen m3=0
gen a2=0
gen a3=0
gen a4=0
gen p2=0
gen p3=0
gen p4=0
gen p5=0
gen e2=0
gen e3=0
gen e4=0

gen CPI=0
replace CPI=69.06667 if qtr==1
replace CPI=71.46667 if qtr==2
replace CPI=73.83333 if qtr==3
replace CPI=75.93333 if qtr==4
replace CPI=78.93333 if qtr==5
replace CPI=81.83333 if qtr==6
replace CPI=83.33333 if qtr==7
replace CPI=85.53333 if qtr==8


replace m2=1 if marital1!=1 & sex_ref==1
replace m3=1 if marital1!=1 & sex_ref==2
	
replace a2=1 if age>=35 & age<=44
replace a3=1 if age>=45 & age<=54
replace a4=1 if age>=55

replace p2=1 if fam_size==2
replace p3=1 if fam_size==3
replace p4=1 if fam_size==4
replace p5=1 if fam_size>=5

replace e2=1 if no_earnr==1
replace e3=1 if no_earnr==2
replace e4=1 if no_earnr>=3
	
replace totexp_fi=fdhome+fdaway+alcbev+util+housop+furnsh+equpmt+mensix+womsix+chldrn+footwr+othapl+vehpch+vehopr+pubtra+health+feeard+tvrdot+persca+educa+tobacc 
replace totexp_fi=log(totexp_fi/CPI) if totexp_fi>0
replace reqown_fi=0.212216+0.727294*totexp_fi+0.064756*m2+0.097039*m3+0.005460*a2-0.06422*a3-0.05059*a4-0.14935*p2-0.19916*p3-0.23904*p4-0.28872*p5-0.04769*e2-0.11144*e3-0.21303*e4
replace reqown=exp(reqown_fi+log(CPI)) if (qtr<=8) & (owndwe>0) & (fincatax>0)
/*reqown adjustment as in Aguir-Bils: END*/


replace shelt=shelt+reqown

gen rec_total_AB=feeard+tvrdot
gen rec_total=rec_audio_video+rec_oth_serv+rec_sport+rec_photo+rec_pets+rec_reading+rec_oth_goods
gen rec_total_narrow=rec_audio_video_narrow+rec_oth_serv_narrow+rec_sport_narrow+rec_photo_narrow+rec_pets_narrow+rec_reading_narrow+rec_oth_goods_narrow
gen totexp=fdhome+fdaway+alcbev+shelt+util+housop+furnsh+equpmt+mensix+womsix+chldrn+footwr+othapl+vehpch+vehopr+pubtra+health+rec_total+persca+educa+tobacc+cashco 


gen fiddrc=reqown-owndwe-owndw2
gen o_fincbtax=fincbtax
gen o_fincatax=fincatax
gen q_finc=reqown-owndwe-owndw2-miscx4-lifins-alchcl+fdmap

  
save temp, replace




global var_list bbyday fdhome fdaway alcbev shelt util housop furnsh equpmt adultcl pubtra chldrn shoesoth vehpch vehopr oppub health feeard tvrdot persca educa tobacc cashco/*
	*/ respstat totexp totexp2 q_finc inc_hrs2 inc_hrs1 rec*
	


forvalues i=2(1)5 {

	use temp, clear
	
	rename intrvw intrvw_`i'
	keep if intrvw_`i'==`i'
	
	if `i'!=5 {
		keep finlwt21 intrvw_`i' id qtr_5th age age2 fam_size no_earnr bls_urbn educ* marital1 sex* $var_list
	}
	if `i'==5 {
		keep finlwt21 intrvw_`i' id qtr_5th age age2 fam_size no_earnr bls_urbn educ* marital1 sex* $var_list /*
		*/ o_fincatax o_fincbtax nonincmx fjssdedx frrdedx fgovretx/*
		*/ fsalaryx perslt18 incweek1 incweek2 region occupre1 occupre2 occucod1 occucod2
	}

	
	foreach variable of varlist $var_list  {
		rename `variable' `variable'_`i'
	}
	
	if `i'==5 {
		gen fincbtax_orig=o_fincbtax
		gen a_fincbtax=o_fincbtax+nonincmx
		gen a_fincatax=o_fincatax+nonincmx-fjssdedx-frrdedx-fgovretx	
		
	}
	
	duplicates drop
	save temp_`i', replace
	

}





use temp_2, replace



merge 1:1 id using temp_3, update replace
drop _merge
merge 1:1 id using temp_4, update replace
drop _merge
merge 1:1 id using temp_5, update replace
drop _merge



gen  year=1980+floor((qtr_5th-2)/4)



local list bbyday fdhome fdaway alcbev shelt util housop furnsh equpmt adultcl chldrn pubtra shoesoth vehpch vehopr oppub health feeard tvrdot persca educa tobacc cashco/*
	*/ respstat totexp totexp2 q_finc inc_hrs2 inc_hrs1 /*
	*/ rec_sport rec_sport_narrow rec_oth_goods rec_oth_goods_narrow rec_photo rec_photo_narrow rec_audio_video rec_audio_video_narrow /*
	*/ rec_oth_serv rec_oth_serv_narrow rec_pets rec_pets_narrow rec_reading rec_reading_narrow rec_cabel rec_total rec_total_narrow rec_total_AB
	
foreach variable of local list  {
	gen `variable'=`variable'_2+`variable'_3+`variable'_4+`variable'_5
}
replace inc_hrs1=inc_hrs1/4
replace inc_hrs2=inc_hrs2/4



gen fincbtax=a_fincbtax+q_finc
gen fincatax=a_fincatax+q_finc

*restrictions
keep if intrvw_2==2 & intrvw_3==3 & intrvw_4==4 & intrvw_5==5
keep if age>=25 & age<=64
keep if respstat_5=="1"
keep if fincatax>=0
keep if (fdhome+fdaway>=0) & (fdhome+fdaway<=totexp)
*exclude those whose consumption of anything but fdhome, fdaway, shelt, vehpch (vehicle purchase) is greater than half of the after tax income
local list alcbev util housop furnsh equpmt adultcl chldrn shoesoth oppub health feeard tvrdot persca educa tobacc cashco
foreach variable of local list  {
	keep if `variable'<0.5*fincatax
}




keep year id finlwt21 qtr_5th age age2 fam_size no_earnr o_finc* fsalaryx fincbtax fincbtax_orig fincatax bls_urbn educ* marital1 sex_ref sex2 bbyday /*
*/ fdhome fdaway alcbev shelt util housop furnsh equpmt adultcl chldrn shoesoth vehpch oppub health feeard tvrdot persca educa tobacc cashco vehopr/*
*/ pubtra totexp totexp2 intrvw_2 perslt18  inc_hrs2 inc_hrs1 incweek1 incweek2 region occupre1 occupre2 occucod1 occucod2/*
*/ rec_sport rec_sport_narrow rec_oth_goods rec_oth_goods_narrow rec_photo rec_photo_narrow rec_audio_video rec_audio_video_narrow /*
*/ rec_oth_serv rec_oth_serv_narrow rec_pets rec_pets_narrow rec_reading rec_reading_narrow rec_cabel rec_total rec_total_narrow rec_total_AB

drop educa_2 educa_3 educa_4 educa_5



*create weights that sum up to 1 each year
sort year
by year: egen tot_weight=total(finlwt21)
gen nweight=finlwt21/tot_weight
drop tot_weight

drop if year==2019


save cex_processed, replace



gen cons_rec=rec_sport+rec_oth_goods+rec_photo+rec_audio_video+rec_oth_serv+rec_pets+rec_reading
gen cons_rec_narrow=rec_sport_narrow+rec_oth_goods_narrow+rec_photo_narrow+rec_audio_video_narrow+rec_oth_serv_narrow+rec_pets_narrow+rec_reading_narrow
gen cons_rec_AB=tvrdot+feeard
gen cons_rec_AB_ext=cons_rec+equpmt

local list2 rec_sport rec_sport_narrow rec_oth_goods rec_oth_goods_narrow rec_photo rec_photo_narrow rec_audio_video rec_audio_video_narrow /*
*/ rec_oth_serv rec_oth_serv_narrow rec_pets rec_pets_narrow rec_reading rec_reading_narrow rec_cabel/*
*/ cons_rec cons_rec_narrow cons_rec_AB cons_rec_AB_ext totexp 

foreach var of local list2 {
	by year: egen `var'_mean=wtmean(`var'), weight(nweight)
	replace `var'=`var'_mean
	drop `var'_mean
}
quietly by year:  gen dup = cond(_N==1,0,_n)	
drop if dup>1
drop dup
keep year rec_sport rec_sport_narrow rec_oth_goods rec_oth_goods_narrow rec_photo rec_photo_narrow rec_audio_video rec_audio_video_narrow /*
*/ rec_oth_serv rec_oth_serv_narrow rec_pets rec_pets_narrow rec_reading rec_reading_narrow rec_cabel/*
*/ cons_rec cons_rec_narrow cons_rec_AB cons_rec_AB_ext totexp 
save rec_cex, replace


erase temp_2.dta
erase temp_3.dta
erase temp_4.dta
erase temp_5.dta
erase temp.dta


